package com.anolesoft.amljmes.foreknow.knowwebservice.mapper;


import com.anolesoft.amljmes.foreknow.knowwebservice.dto.MesBzzFcfhjhDtoDao;
import com.anolesoft.amljmes.fortysix.dto.MesBzzPtmlDto;
import org.apache.ibatis.annotations.*;

@Mapper
public interface QjwlWebMapper {
    /**
     * 验证检验条码是否为检验
     */
    @Select("SELECT t.eid\n" +
            "  FROM STF_EMPLOYEE t\n" +
            " WHERE t.PASSWORD2 = #{password2, jdbcType = VARCHAR}\n" +
            "   AND NOW_QUARTERS LIKE '%检验%'\n" +
            "   AND t.delete_flag = 0\n" +
            "   AND t.factory_id = #{fcode, jdbcType = VARCHAR}")
    String jianyan(@Param("password2")String password2, @Param("fcode")String fcode);

    /**
     * 全景物流传回数据查询ptml的封存类型和是否做防护返回封存类型
     */
    @Select("select m.*,p.jixing,p.tuhao,p.jiaofuchejian,p.jieshouchejian,p.jiaofushunxu,p.fengcunfangfa " +
            "from MES_BZZ_PTML_INS m left join mes_bzz_ptml p on m.wuliaohao = p.wuliaohao " +
            "where m.wuliaohao = (select wuliaohao from MES_BZZ_PTML " +
            "where JIXING = #{jixing, jdbcType=VARCHAR} " +
            " and TUHAO = #{tuhao, jdbcType=VARCHAR} " +
            " and JIAOFUCHEJIAN = #{jiaofuchejian, jdbcType=VARCHAR} " +
            " and JIESHOUCHEJIAN = #{jieshouchejian, jdbcType=VARCHAR} " +
            " and JIAOFUSHUNXU = #{jiaofushunxu, jdbcType=VARCHAR}) " +
            " and p.jixing = #{jixing, jdbcType=VARCHAR} " +
            " and TUHAO = #{tuhao, jdbcType=VARCHAR} ")
    MesBzzPtmlDto toPtml(MesBzzPtmlDto mesBzzPtmlDto);
    /**
     * 全景物流传回数据查询ptml的封存类型和是否做防护返回封存类型
     */
    @Select("select count(objuid) from MES_BZZ_PTML_INS " +
            " where wuliaohao = (select wuliaohao from MES_BZZ_PTML " +
            " where JIXING = #{jixing, jdbcType=VARCHAR} " +
            " and TUHAO = #{tuhao, jdbcType=VARCHAR} " +
            " and JIAOFUCHEJIAN = #{jiaofuchejian, jdbcType=VARCHAR} " +
            " and JIESHOUCHEJIAN = #{jieshouchejian, jdbcType=VARCHAR} " +
            " and JIAOFUSHUNXU = #{jiaofushunxu, jdbcType=VARCHAR}" +
            " group by wuliaohao) ")
    int countPtml(MesBzzPtmlDto mesBzzPtmlDto);
//            "where JIXING = #{jixing, jdbcType=VARCHAR} " +
//            "and TUHAO = #{tuhao, jdbcType=VARCHAR} " +
//            "and JIAOFUCHEJIAN = #{jiaofuchejian, jdbcType=VARCHAR} " +
//            "and JIESHOUCHEJIAN = #{jieshouchejian, jdbcType=VARCHAR} " +
//            "and JIAOFUSHUNXU = #{jiaofushunxu, jdbcType=VARCHAR} " +
//            "and CHANGDAIHAO = #{changdaihao, jdbcType=VARCHAR}")

    /**
     * 根据封存类型向fcfhjh表中插入数据防护
     */
    @Insert("insert into MES_BZZ_FCFHJH(QUALIFIED_LABEL_NUMBER,PLAN_TYPE," +
            "STATE,C_USER,C_TIME,FACTORY,PAIGONGHAO,JIXING,TUHAO," +
            "JIAOFUCHEJIAN,JIESHOUCHEJIAN,JIAOFUSHUNXU,KUCUN_OBJUID,SHULIANG,EXPIRATION_TIME,MINGCHENG,DW,PC,JC) " +
            "VALUES(#{qualifiedLabelNumber, jdbcType=VARCHAR},#{planType, jdbcType=NUMERIC}," +
            "#{state, jdbcType=NUMERIC},#{cUser, jdbcType=VARCHAR},sysdate,#{factory, jdbcType=VARCHAR}," +
            "#{paigonghao, jdbcType=VARCHAR},#{jixing, jdbcType=VARCHAR},#{tuhao, jdbcType=VARCHAR}," +
            "#{jiaofuchejian, jdbcType=VARCHAR},#{jieshouchejian, jdbcType=VARCHAR},#{jiaofushunxu, jdbcType=VARCHAR}," +
            "#{kucunObjuid, jdbcType=VARCHAR},#{shuliang, jdbcType=NUMERIC},to_date(#{fanghuTime},'YYYY/MM/DD hh24:mi:ss')," +
            "#{mingcheng, jdbcType=NUMERIC},#{dw, jdbcType=VARCHAR},#{pc, jdbcType=VARCHAR},#{jc, jdbcType=VARCHAR})")
    int insertFcfhjhfanghu(MesBzzFcfhjhDtoDao mesBzzFcfhjhDtoDao);

    /**
     * 根据封存类型向fcfhjh表中插入数据封存
     */
    @Insert("insert into MES_BZZ_FCFHJH(QUALIFIED_LABEL_NUMBER,EXPIRATION_TIME,PLAN_TYPE,SEALING_METHOD," +
            "INTERNAL_DIVISION_OF_LABOR,SEAL_TYPE,STATE,C_USER,C_TIME,FACTORY,PAIGONGHAO,JIXING,TUHAO," +
            "JIAOFUCHEJIAN,JIESHOUCHEJIAN,JIAOFUSHUNXU,KUCUN_OBJUID,SHULIANG,MINGCHENG,DW,PC,JC) " +
            "VALUES(#{qualifiedLabelNumber, jdbcType=VARCHAR},to_date(#{expirationTime},'YYYY/MM/DD hh24:mi:ss'),#{planType, jdbcType=NUMERIC}," +
            "#{sealingMethod, jdbcType=VARCHAR},#{internalDivisionOfLabor, jdbcType=VARCHAR},#{sealType, jdbcType=NUMERIC}, " +
            "#{state, jdbcType=NUMERIC},#{cUser, jdbcType=VARCHAR},sysdate,#{factory, jdbcType=VARCHAR}," +
            "#{paigonghao, jdbcType=VARCHAR},#{jixing, jdbcType=VARCHAR},#{tuhao, jdbcType=VARCHAR}," +
            "#{jiaofuchejian, jdbcType=VARCHAR},#{jieshouchejian, jdbcType=VARCHAR},#{jiaofushunxu, jdbcType=VARCHAR}," +
            "#{kucunObjuid, jdbcType=VARCHAR},#{shuliang, jdbcType=NUMERIC},#{mingcheng, jdbcType=NUMERIC},#{dw, jdbcType=VARCHAR}," +
            "#{pc, jdbcType=VARCHAR},#{jc, jdbcType=VARCHAR})")
    int insertFcfhjhfengcuyn(MesBzzFcfhjhDtoDao mesBzzFcfhjhDtoDao);
    /**
     * 根据qjwl制造记录条码去mes查询合格标签条码(根据制造记录条码去查order的计划主键,是fcfhjh的主键,拿到合格标签编号)
     */
    @Select("select f.qualified_label_number from mes_bzz_fcfhjh f where objuid =" +
            " (select planuid from mes_bzz_order where barcode = #{zzjltm, jdbcType=VARCHAR})")
    String hgbqtm(String zzjltm);

    /**
     * 根据合格标签编号获取数据(内部封存入库)
     */
    @Select("select o.objuid,to_char(o.storageperiod,'YYYY/MM/DD') storageperiod,f.sealing_method,f.paigonghao,f.jixing,f.tuhao,f.mingcheng," +
            "f.shuliang,f.jiaofuchejian,f.qualified_label_number,f.kucun_objuid " +
            "from mes_bzz_order o " +
            "left join mes_bzz_fcfhjh f " +
            "on o.planuid = f.objuid " +
            "where o.state > 2 " +
            "and o.sealsignatureno is not null " +
            "and o.rk_zt = 0 and o.deletetag = 1" +
            "and f.qualified_label_number = #{hgbqtm, jdbcType=VARCHAR}")
    MesBzzFcfhjhDtoDao getDataByhgbq(String hgbqtm);

    /**
     * 修改封存防护计划状态
     */
    @Update("update MES_BZZ_FCFHJH set STATE = 3, wanchengren = #{wcr}, wanchengshijian = sysdate where plan_type = 0 and QUALIFIED_LABEL_NUMBER = #{hgbqtm, jdbcType=VARCHAR}")
    int updateFcfhjh(@Param("hgbqtm") String hgbqtm, @Param("wcr") String wcr);

    /**
     * 修改order表状态为已出库
     */
    @Update("update mes_bzz_order t set t.STATE = 6 where t.planuid = (select objuid from MES_BZZ_FCFHJH where plan_type = 0 and QUALIFIED_LABEL_NUMBER = #{hgbqtm, jdbcType=VARCHAR})")
    int updateOrderState(@Param("hgbqtm") String hgbqtm);

    /**
     * 判断是否是第二次入库,第二次的不生成防护计划
     */
    @Select("select count(objuid) from mes_bzz_fcfhjh t where t.qualified_label_number = #{qualifiedLabelNumber, jdbcType=VARCHAR} and t.plan_type = 1 ")
    int countfh(MesBzzFcfhjhDtoDao dtoDao);

    /**
     * 判断order表中是否存在数据,存在更改入库状态
     */
    @Select("select count(objuid) from mes_bzz_order t where t.objuid = #{objuid, jdbcType=VARCHAR} ")
    int countorder(String objuid);
    @Update("update mes_bzz_order t set t.rk_zt = 1 where t.objuid = #{objuid, jdbcType=VARCHAR} ")
    int updateorder(String objuid);

}
